CREATE TABLE RECIPE(
R_NO NUMBER PRIMARY KEY,
R_TYPE VARCHAR2(30) NOT NULL,
R_TITLE VARCHAR2(100) NOT NULL,
R_IMG VARCHAR2(100) NOT NULL,
R_COMMENT VARCHAR2(100) NOT NULL,
R_CNT NUMBER DEFAULT 0,
R_MCNT NUMBER NOT NULL,
R_MAIN_INGRE VARCHAR2(150) NOT NULL,
R_SUB_INGRE VARCHAR2(150) NOT NULL,
R_TOTALGRADE NUMBER DEFAULT 0,
R_GCNT NUMBER DEFAULT 0,
R_DATE DATE NOT NULL,
CONSTRAINT FK_R_TYPE FOREIGN KEY(R_TYPE) REFERENCES RECIPE_TYPE
);
--외래키는 PRIMARY와 UNIQUE만 참조가능
--UNIQUE일 경우 CONSTRAINT FK_R_TYPE FOREIGN KEY(R_TYPE) REFERENCES RECIPE(R_TYPE)
create SEQUENCE R_NO_SEQ;
SELECT R_NO_SEQ.NEXTVAL FROM DUAL;
drop TABLE RECIPE;
DROP SEQUENCE R_NO_SEQ;
SELECT * FROM RECIPE
drop TABLE RECIPE_TYPE;

CREATE TABLE RECIPE_TYPE(
R_TYPE VARCHAR2(30) PRIMARY KEY,
R_KIND VARCHAR2(50) NOT NULL
 );
 DROP TABLE RECIPE_DESC
 CREATE table RECIPE_DESC(
 	R_ORDER NUMBER default 0,
 	R_NO NUMBER NOT NULL,
 	R_SUBJECT VARCHAR2(50) NOT NULL,
 	R_IMG_PATH VARCHAR2(100) NOT NULL,
 	R_CONTENT VARCHAR2(4000) NOT NULL,
 	CONSTRAINT FK_R_NO FOREIGN KEY(R_NO) REFERENCES RECIPE,
 	constraint pk_RECIPE_DESC primary key(R_NO,R_ORDER)
 )
 DROP TABLE MEMBER;
 CREATE TABLE MEMBER(
M_ID VARCHAR2(50)  PRIMARY KEY,
M_PASSWORD VARCHAR2(50) NOT NULL,
M_EMAIL VARCHAR2(50) NOT NULL,
M_LIKECOOK NUMBER NOT NULL,
M_MARRY VARCHAR2(50) NOT NULL,
M_GENDER VARCHAR2(50) NOT NULL,
M_CONTRI NUMBER NOT NULL,
M_TYPE VARCHAR2(50) NOT NULL
)

CREATE TABLE LIKECOOK(
LIKECOOKNO NUMBER,
M_ID VARCHAR2(50),
LIKECOOK_NAME VARCHAR2(50),
 	CONSTRAINT FK_M_ID FOREIGN KEY(M_ID) REFERENCES MEMBER,
 	constraint pk_LIKECOOK primary key(M_ID,LIKECOOKNO)
)

CREATE TABLE ANNIVERSARY(
A_NO NUMBER,
M_ID VARCHAR2(50),
A_DATE VARCHAR2(50) NOT NULL,
 	CONSTRAINT FK_A_M_ID FOREIGN KEY(M_ID) REFERENCES MEMBER,
 	constraint pk_ANNIVERSARY primary key(M_ID,A_NO)
)

CREATE TABLE QNABOARD(
	B_NO NUMBER PRIMARY KEY,
	B_TITLE VARCHAR2(50) NOT NULL,
	B_CONTENT VARCHAR2(4000) NOT NULL,
	B_DATE DATE NOT NULL,
	B_CNT NUMBER DEFAULT 0,
	B_REF NUMBER NOT NULL,
	B_RESTEP NUMBER NOT NULL,
	B_RELEVEL NUMBER NOT NULL,
	M_ID VARCHAR2(50),
	CONSTRAINT FK_Q_M_ID FOREIGN KEY(M_ID) REFERENCES MEMBER
)
 
CREATE TABLE FREEBOARD(
	B_NO NUMBER PRIMARY KEY,
	B_TITLE VARCHAR2(50) NOT NULL,
	B_CONTENT VARCHAR2(4000) NOT NULL,
	B_DATE DATE NOT NULL,
	B_CNT NUMBER DEFAULT 0,
	B_REF NUMBER NOT NULL,
	B_RESTEP NUMBER NOT NULL,
	B_RELEVEL NUMBER NOT NULL,
	M_ID VARCHAR2(50),
	CONSTRAINT FK_F_M_ID FOREIGN KEY(M_ID) REFERENCES MEMBER
)


CREATE TABLE NOTICE(
	B_NO NUMBER PRIMARY KEY,
	B_TITLE VARCHAR2(50) NOT NULL,
	B_CONTENT VARCHAR2(4000) NOT NULL,
	B_DATE DATE NOT NULL,
	B_CNT NUMBER DEFAULT 0,
	B_REF NUMBER NOT NULL,
	B_RESTEP NUMBER NOT NULL,
	B_RELEVEL NUMBER NOT NULL,
	M_ID VARCHAR2(50),
	CONSTRAINT FK_N_M_ID FOREIGN KEY(M_ID) REFERENCES MEMBER
)
 
 
 